from flask import Blueprint, request, jsonify, send_file
from models import db, CertificateName
import openpyxl
from openpyxl.utils import get_column_letter
from io import BytesIO
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

certificate_name_bp = Blueprint('certificate_name', __name__)

# @certificate_name_bp.route('/api/certificate-names', methods=['GET'])
# def get_certificate_names():
#     names = CertificateName.query.all()
#     return jsonify([n.to_dict() for n in names])

# @certificate_name_bp.route('/api/certificate-names/<int:id>', methods=['GET'])
# def get_certificate_name(id):
#     n = CertificateName.query.get_or_404(id)
#     return jsonify(n.to_dict())

# @certificate_name_bp.route('/api/certificate-names', methods=['POST'])
# def create_certificate_name():
#     data = request.get_json()
#     n = CertificateName(**data)
#     db.session.add(n)
#     db.session.commit()
#     return jsonify({'message': '创建成功', 'id': n.id}), 201

# @certificate_name_bp.route('/api/certificate-names/<int:id>', methods=['PUT'])
# def update_certificate_name(id):
#     n = CertificateName.query.get_or_404(id)
#     data = request.get_json()
#     for key, value in data.items():
#         setattr(n, key, value)
#     db.session.commit()
#     return jsonify({'message': '修改成功'})

# @certificate_name_bp.route('/api/certificate-names/<int:id>', methods=['DELETE'])
# def delete_certificate_name(id):
#     n = CertificateName.query.get_or_404(id)
#     db.session.delete(n)
#     db.session.commit()
#     return jsonify({'message': '删除成功'})

@certificate_name_bp.route('/api/certificate-names/export/excel', methods=['GET'])
def export_certificate_names_excel():
    try:
        names = CertificateName.query.filter_by(is_deleted=False).all()
        fields = [
            ('name', '证书名称'),
            ('level', '级别'),
            ('issuing_org', '颁发机构'),
        ]
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = '证书名称列表'
        # 样式定义
        header_font = Font(bold=True, color='FFFFFF')
        header_fill = PatternFill('solid', fgColor='4F81BD')
        center_alignment = Alignment(horizontal='center', vertical='center')
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        # 写入表头
        for col, (_, header) in enumerate(fields, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = center_alignment
            cell.border = thin_border
        # 写入数据
        for row, n in enumerate(names, 2):
            for col, (field, _) in enumerate(fields, 1):
                value = getattr(n, field, '')
                cell = ws.cell(row=row, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
        # 自动调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if cell.value:
                        max_length = max(max_length, len(str(cell.value)))
                except:
                    pass
            ws.column_dimensions[column].width = max_length + 4
        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name='证书名称列表.xlsx',
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    except Exception as e:
        return jsonify({'error': str(e)}), 500 